package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import modelo.Cliente;

public class DaoCliente {
	private Connection c;
	private PreparedStatement ps = null;
	private ResultSet r = null;

	public DaoCliente() throws ClassNotFoundException, SQLException {
		super();
		c = Conexao.getConexao();
	}

	public int obterIdPessoa() throws SQLException, Exception {
		int i = 0;
		ps = c.prepareStatement("select max(idpessoa) from pessoa");
		r = ps.executeQuery();
		if (r.next())
			i = r.getInt(1);
		return i;
	}

	public int obterIdCliente() throws SQLException, Exception {
		int i = 0;
		ps = c.prepareStatement("select max(codigo) from cliente");
		r = ps.executeQuery();
		if (r.next())
			i = r.getInt(1);
		return i;
	}

	public void gravarPessoa(Cliente cl) throws SQLException, Exception {
		ps = c.prepareStatement("insert into pessoa values (?,?,?,?,?,?,?,?,?)");
		ps.setInt(1, cl.getIdPessoa());
		ps.setString(2, cl.getNome());
		ps.setString(3, cl.getLogradouro());
		ps.setString(4, cl.getNumero());
		ps.setString(5, cl.getBairro());
		ps.setString(6, cl.getTelefone());
		ps.setString(7, cl.getRg());
		ps.setString(8, cl.getOrgaoEmissor());
		ps.setString(9, cl.getComplemento());
		ps.executeUpdate();
	}

	public void gravarCliente(Cliente cl) throws SQLException, Exception {
		ps = c.prepareStatement("insert into cliente values (?,?)");
		ps.setInt(1, cl.getCodigo());
		ps.setInt(2, cl.getIdPessoa());
		ps.executeUpdate();
	}

	public void alterarCliente(Cliente cl) throws SQLException, Exception
{
ps = c.prepareStatement("update pessoa set nome=?,logradouro=?,numero=?,bairro=?,telefone=?, rg=?,orgaoemissor=?, complemento=? where idpessoa in (select idpessoa from cliente where codigo = ?)");
ps.setString(1, cl.getNome());
ps.setString(2, cl.getLogradouro());
ps.setString(3, cl.getNumero());
ps.setString(4, cl.getBairro());
ps.setString(5, cl.getTelefone());
ps.setString(6, cl.getRg());
ps.setString(7, cl.getOrgaoEmissor());
ps.setString(8, cl.getComplemento());
ps.setInt(9, cl.getCodigo());
ps.executeUpdate();
/*
ps = c.prepareStatement("update cliente set rg=?,orgaoemissor=? where idcliente=?");
//ps.setString(1, cl.getRg());
//ps.setString(2, cl.getOrgaoEmissor());
ps.setInt(3, cl.getCodigo());
ps.executeUpdate();
*/
}

	public void excluirCliente(Cliente cl) throws SQLException, Exception {
		ps = c.prepareStatement("delete from cliente where codigo=?");
		ps.setInt(1, cl.getCodigo());
		ps.executeUpdate();
	}

	public ArrayList<Cliente> consultarCliente(Cliente cl) throws SQLException, Exception
{ //ArrayList<Pessoa> lista = new ArrayList<Pessoa>();
ArrayList<Cliente> lista = new ArrayList<Cliente>();
ps = c.prepareStatement("select cliente.codigo, pessoa.* from cliente, pessoa where cliente.idpessoa = pessoa.idpessoa and cliente.idpessoa in (select idpessoa from pessoa where nome ilike ?)order by nome");
ps.setString(1, cl.getNome()+ "%");
r = ps.executeQuery();
while (r.next())
{
	cl = new Cliente();
	cl.setIdPessoa(r.getInt("idpessoa"));
	cl.setCodigo(r.getInt("codigo"));
	cl.setNome(r.getString("nome"));
	cl.setLogradouro(r.getString("logradouro"));
	cl.setNumero(r.getString("numero"));
	cl.setBairro(r.getString("bairro"));
	cl.setTelefone(r.getString("telefone"));
	cl.setRg(r.getString("rg"));
	cl.setOrgaoEmissor(r.getString("orgaoemissor"));
	cl.setComplemento(r.getString("complemento"));
	lista.add(cl);
	}
	return lista;
	}

	public Cliente buscarPessoa(String rg) throws SQLException, Exception {
		Cliente cl = null;
		ps = c.prepareStatement("select * from pessoa where rg=? ");
		ps.setString(1, rg);
		r = ps.executeQuery();
		if (r.next()) {
			cl = new Cliente();
			cl.setIdPessoa(r.getInt("idpessoa"));
			cl.setNome(r.getString("nome"));
			cl.setLogradouro(r.getString("logradouro"));
			cl.setNumero(r.getString("numero"));
			cl.setBairro(r.getString("bairro"));
			cl.setTelefone(r.getString("telefone"));
			cl.setRg(r.getString("rg"));
			cl.setOrgaoEmissor(r.getString("orgaoemissor"));
			cl.setComplemento(r.getString("complemento"));
		}
		return cl;
	}

	public boolean buscarCliente(int codP) throws SQLException, Exception {
		boolean resultado = false;
		ps = c.prepareStatement("select codigo from cliente where idpessoa=? ");
		ps.setInt(1, codP);
		r = ps.executeQuery();
		if (r.next()) {
			resultado = true;
		}
		return resultado;
	}

	public void desfazerBanco() throws SQLException, Exception {
		c.rollback();
	}

	public void finalize() throws SQLException {
		if (r != null) {
			r.close();
			ps.close();
		}
		c.close();
	}
}// fim do dao